﻿---更新养护机械表
ALTER TABLE [dbo].[MaintainMachine] ADD 
[MaintenanceCost] [decimal] (18,3) NOT NULL CONSTRAINT [DF_MaintainMachine_MaintenanceCost] DEFAULT ((0))
GO

---更新工程机械表
ALTER TABLE [dbo].[ProjectMachine] ADD 
[MaintenanceCost] [decimal] (18,2) NOT NULL CONSTRAINT [DF_ProjectMachine_MaintenanceCost] DEFAULT ((0))
GO

---更新养护工程量表
ALTER TABLE [dbo].[MaintainQuantity] ADD Comment nvarchar(MAX) NULL
GO

---更新养护计划表
ALTER TABLE [dbo].[MaintainPlan] ADD Comment nvarchar(MAX) NULL
GO

---更新养护日志表
ALTER TABLE [dbo].[MaintainJournal] ADD Comment nvarchar(MAX) NULL
GO

---更新养护人工表
ALTER TABLE [dbo].[MaintainLabour] ADD Comment nvarchar(MAX) NULL
GO

---更新养护机械表
ALTER TABLE [dbo].[MaintainMachine] ADD Comment nvarchar(MAX) NULL
GO

---更新养护材料表
ALTER TABLE [dbo].[MaintainMaterial] ADD Comment nvarchar(MAX) NULL
GO

---更新工程工程量表
ALTER TABLE [dbo].[ProjectQuantity] ADD Comment nvarchar(MAX) NULL
GO

---更新工程计划表
ALTER TABLE [dbo].[ProjectPlan] ADD Comment nvarchar(MAX) NULL
GO

---更新工程日志表
ALTER TABLE [dbo].[ProjectJournal] ADD Comment nvarchar(MAX) NULL
GO

---更新工程人工表
ALTER TABLE [dbo].[ProjectLabour] ADD Comment nvarchar(MAX) NULL
GO

---更新工程机械表
ALTER TABLE [dbo].[ProjectMachine] ADD Comment nvarchar(MAX) NULL
GO

---更新工程材料表
ALTER TABLE [dbo].[ProjectMaterial] ADD Comment nvarchar(MAX) NULL
GO

--创建养护汇总表查询视图
--删除视图
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'MaintainSummary' AND xtype = 'V' )
BEGIN
	DROP VIEW MaintainSummary
END
GO

--创建视图
CREATE VIEW MaintainSummary 
AS
SELECT O.OrganizationName AS SubSectionName, O.OrganizationCode AS SubSectionCode, T.*
FROM
(
	SELECT CASE O.Category WHEN 2 THEN ISNULL( O.ParentID, O.OrganizationID ) ELSE O.OrganizationID END AS SubSectionID, Q.OrganizationID AS GroupID, O.OrganizationCode AS GroupCode, O.OrganizationName AS GroupNmae, Q.QuantityID, Q.MaintainCode, Q.Quantity AS MaintainQuantity, 
	C.Unit AS MaintainUnit, Q.Price AS MaintainPrice, Q.Amount As MaintainAmount, DataType = 0, DataTypeName = '人工', 
	L.JobID As Category, DJ.ItemName AS CategoryName, L.LabourTypeID AS SubCategory, DLT.ItemName AS SubCategoryName,
	L.Quantity, L.Price, L.Amount, L.LabourTypeID AS UnitID, '天' AS Unit, Q.ApplicationDate AS QuantityApplicationDate, L.ApplicationDate
	FROM MaintainQuantity Q
	INNER JOIN Organization O
	ON Q.OrganizationID = O.OrganizationID
	INNER JOIN MaintainCategory C
	ON Q.CategoryID = C.CategoryID
	INNER JOIN MaintainLabour L
	ON Q.QuantityID = L.QuantityID
	INNER JOIN DictionayItem DJ
	ON L.JobID = DJ.ItemID AND DJ.DictionayID = 5
	INNER JOIN DictionayItem DLT
	ON L.LabourTypeID = DLT.ItemID AND DLT.DictionayID = 1
	WHERE Q.[State] = 500 AND L.[State] = 500 AND L.Amount > 0
	UNION
	SELECT CASE O.Category WHEN 2 THEN ISNULL( O.ParentID, O.OrganizationID ) ELSE O.OrganizationID END AS SubSectionID, Q.OrganizationID AS GroupID, O.OrganizationCode AS GroupCode, O.OrganizationName AS GroupNmae, Q.QuantityID, Q.MaintainCode, Q.Quantity AS MaintainQuantity, 
	C.Unit AS MaintainUnit, Q.Price AS MaintainPrice, Q.Amount As MaintainAmount, DataType = 1, DataTypeName = '材料', 
	M.MaterialID As Category, M.MaterialName AS CategoryName, M.MaterialID AS SubCategory, '/' AS SubCategoryName,
	MM.Quantity, MM.Price, MM.Amount, M.MaterialID AS UnitID, M.Unit, Q.ApplicationDate AS QuantityApplicationDate, MM.ApplicationDate
	FROM MaintainQuantity Q
	INNER JOIN Organization O
	ON Q.OrganizationID = O.OrganizationID	
	INNER JOIN MaintainCategory C
	ON Q.CategoryID = C.CategoryID
	INNER JOIN MaintainMaterial MM
	ON Q.QuantityID = MM.QuantityID
	INNER JOIN Material M
	ON MM.MaterialID = M.MaterialID
	WHERE Q.[State] = 500 AND MM.[State] = 500 AND MM.Amount > 0 
	UNION
	SELECT CASE O.Category WHEN 2 THEN ISNULL( O.ParentID, O.OrganizationID ) ELSE O.OrganizationID END AS SubSectionID, Q.OrganizationID AS GroupID, O.OrganizationCode AS GroupCode, O.OrganizationName AS GroupNmae, Q.QuantityID, Q.MaintainCode, Q.Quantity AS MaintainQuantity, 
	C.Unit AS MaintainUnit, Q.Price AS MaintainPrice, Q.Amount As MaintainAmount, DataType = 2, DataTypeName = '机械', 
	MMA.MachineID As Category, DM.ItemName AS CategoryName, MMA.MachineTypeID AS SubCategory, DMT.ItemName AS SubCategoryName,
	MMA.Quantity, MMA.Price, MMA.Amount, DU.ItemID AS UnitID, DU.ItemName AS Unit, Q.ApplicationDate AS QuantityApplicationDate, MMA.ApplicationDate
	FROM MaintainQuantity Q
	INNER JOIN Organization O
	ON Q.OrganizationID = O.OrganizationID	
	INNER JOIN MaintainMachine MMA
	ON Q.QuantityID = MMA.QuantityID
	INNER JOIN MaintainCategory C
	ON Q.CategoryID = C.CategoryID
	INNER JOIN DictionayItem DM
	ON MMA.MachineID = DM.ItemID AND DM.DictionayID = 7
	INNER JOIN DictionayItem DMT
	ON MMA.MachineTypeID = DMT.ItemID AND DMT.DictionayID = 9
	INNER JOIN DictionayItem DU
	ON MMA.MachineUnitID = DU.ItemID AND DU.DictionayID = 6
	WHERE Q.[State] = 500 AND MMA.[State] = 500 AND MMA.Amount > 0 
	UNION	
	SELECT CASE O.Category WHEN 2 THEN ISNULL( O.ParentID, O.OrganizationID ) ELSE O.OrganizationID END AS SubSectionID, Q.OrganizationID AS GroupID, O.OrganizationCode AS GroupCode, O.OrganizationName AS GroupNmae, Q.QuantityID, Q.MaintainCode, Q.Quantity AS MaintainQuantity, 
	C.Unit AS MaintainUnit, Q.Price AS MaintainPrice, Q.Amount As MaintainAmount, DataType = 3, DataTypeName = '油料', 
	MMA.MachineID As Category, DM.ItemName AS CategoryName, MMA.OilType AS SubCategory, DO.ItemName AS SubCategoryName,
	MMA.OilWear AS Quantity, MMA.OilPrice AS Price, MMA.OilAmount AS Amount, MMA.OilType AS UnitID, '升' AS Unit, Q.ApplicationDate AS QuantityApplicationDate, MMA.ApplicationDate
	FROM MaintainQuantity Q
	INNER JOIN Organization O
	ON Q.OrganizationID = O.OrganizationID	
	INNER JOIN MaintainMachine MMA
	ON Q.QuantityID = MMA.QuantityID
	INNER JOIN MaintainCategory C
	ON Q.CategoryID = C.CategoryID
	INNER JOIN DictionayItem DM
	ON MMA.MachineID = DM.ItemID AND DM.DictionayID = 7
	INNER JOIN DictionayItem DO
	ON MMA.OilType = DO.ItemID AND DO.DictionayID = 13
	WHERE Q.[State] = 500 AND MMA.[State] = 500	AND MMA.OilAmount > 0
	UNION
	SELECT CASE O.Category WHEN 2 THEN ISNULL( O.ParentID, O.OrganizationID ) ELSE O.OrganizationID END AS SubSectionID, Q.OrganizationID AS GroupID, O.OrganizationCode AS GroupCode, O.OrganizationName AS GroupNmae, Q.QuantityID, Q.MaintainCode, Q.Quantity AS MaintainQuantity, 
	C.Unit AS MaintainUnit, Q.Price AS MaintainPrice, Q.Amount As MaintainAmount, DataType = 4, DataTypeName = '维修费用', 
	MMA.MachineID As Category, DM.ItemName AS CategoryName, MMA.MachineTypeID AS SubCategory, DMT.ItemName AS SubCategoryName,
	0 AS Quantity, 0 AS Price, MMA.MaintenanceCost AS Amount, MMA.MachineTypeID AS UnitID, '/' AS Unit, Q.ApplicationDate AS QuantityApplicationDate, MMA.ApplicationDate
	FROM MaintainQuantity Q
	INNER JOIN Organization O
	ON Q.OrganizationID = O.OrganizationID	
	INNER JOIN MaintainMachine MMA
	ON Q.QuantityID = MMA.QuantityID
	INNER JOIN MaintainCategory C
	ON Q.CategoryID = C.CategoryID
	INNER JOIN DictionayItem DM
	ON MMA.MachineID = DM.ItemID AND DM.DictionayID = 7
	INNER JOIN DictionayItem DMT
	ON MMA.MachineTypeID = DMT.ItemID AND DMT.DictionayID = 9
	WHERE Q.[State] = 500 AND MMA.[State] = 500	AND MMA.MaintenanceCost > 0
) AS T
INNER JOIN Organization O
ON T.SubSectionID = O.OrganizationID
GO

--创建项目汇总表查询视图
--删除视图
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'ProjectSummary' AND xtype = 'V' )
BEGIN
	DROP VIEW ProjectSummary
END
GO

--创建视图
CREATE VIEW ProjectSummary 
AS
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.CategoryID AS ProjectCategory, P.[State], P.StartDate, P.CompletionDate, P.Budget, P.ApprovedBudget,
Q.Quantity AS ProjectQuantity, C.Unit AS ProjectUnit, Q.Price AS ProjectPrice, Q.Amount AS ProjectAmount, C.ConstructionID, C.ConstructionName,
DataType = 0, DataTypeName = '人工', L.JobID As Category, DJ.ItemName AS CategoryName, L.LabourTypeID AS SubCategory, DLT.ItemName AS SubCategoryName,
L.Quantity, L.Price, L.LabourTypeID AS UnitID, '天' AS Unit, L.Amount, L.ApplicationDate, O.OrganizationID, O.OrganizationCode, O.OrganizationName
FROM ProjectQuantity Q
INNER JOIN Project P
ON Q.ProjectID = P.ProjectID
INNER JOIN Organization O
ON P.OrganizationID = O.OrganizationID
INNER JOIN Construction C
ON Q.ConstructionID = C.ConstructionID
INNER JOIN ProjectLabour L
ON Q.QuantityID = L.QuantityID
INNER JOIN DictionayItem DJ
ON L.JobID = DJ.ItemID AND DJ.DictionayID = 5
INNER JOIN DictionayItem DLT
ON L.LabourTypeID = DLT.ItemID AND DLT.DictionayID = 1
WHERE Q.[State] = 500 AND L.[State] = 500 AND L.Amount > 0
UNION 
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.CategoryID AS ProjectCategory, P.[State], P.StartDate, P.CompletionDate, P.Budget, P.ApprovedBudget,
Q.Quantity AS ProjectQuantity, C.Unit AS ProjectUnit, Q.Price AS ProjectPrice, Q.Amount AS ProjectAmount, C.ConstructionID, C.ConstructionName,
DataType = 1, DataTypeName = '材料', PM.MaterialID As Category, M.MaterialName AS CategoryName, M.MaterialID AS SubCategory, '/' AS SubCategoryName,
PM.Quantity, PM.Price, M.MaterialID AS UnitID, M.Unit, PM.Amount, PM.ApplicationDate, O.OrganizationID, O.OrganizationCode, O.OrganizationName
FROM ProjectQuantity Q
INNER JOIN Project P
ON Q.ProjectID = P.ProjectID
INNER JOIN Organization O
ON P.OrganizationID = O.OrganizationID
INNER JOIN Construction C
ON Q.ConstructionID = C.ConstructionID
INNER JOIN ProjectMaterial PM
ON Q.QuantityID = PM.QuantityID
INNER JOIN Material M
ON PM.MaterialID = M.MaterialID
WHERE Q.[State] = 500 AND PM.[State] = 500 AND PM.Amount > 0
UNION
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.CategoryID AS ProjectCategory, P.[State], P.StartDate, P.CompletionDate, P.Budget, P.ApprovedBudget,
Q.Quantity AS ProjectQuantity, C.Unit AS ProjectUnit, Q.Price AS ProjectPrice, Q.Amount AS ProjectAmount, C.ConstructionID, C.ConstructionName,
DataType = 2, DataTypeName = '机械', PM.MachineID As Category, DM.ItemName AS CategoryName, PM.MachineUnitID AS SubCategory, '/' AS SubCategoryName,
PM.Quantity, PM.Price, PM.MachineUnitID AS UnitID, DU.ItemName AS Unit, PM.Amount, PM.ApplicationDate, O.OrganizationID, O.OrganizationCode, O.OrganizationName
FROM ProjectQuantity Q
INNER JOIN Project P
ON Q.ProjectID = P.ProjectID
INNER JOIN Organization O
ON P.OrganizationID = O.OrganizationID
INNER JOIN Construction C
ON Q.ConstructionID = C.ConstructionID
INNER JOIN ProjectMachine PM
ON Q.QuantityID = PM.QuantityID
INNER JOIN DictionayItem DM
ON PM.MachineID = DM.ItemID AND DM.DictionayID = 7
INNER JOIN DictionayItem DU
ON PM.MachineUnitID = DU.ItemID AND DU.DictionayID = 6
WHERE Q.[State] = 500 AND PM.[State] = 500 AND PM.Amount > 0 
UNION
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.CategoryID AS ProjectCategory, P.[State], P.StartDate, P.CompletionDate, P.Budget, P.ApprovedBudget,
Q.Quantity AS ProjectQuantity, C.Unit AS ProjectUnit, Q.Price AS ProjectPrice, Q.Amount AS ProjectAmount, C.ConstructionID, C.ConstructionName,
DataType = 3, DataTypeName = '油料', PM.MachineID As Category, DM.ItemName AS CategoryName, PM.OilType AS SubCategory, PO.ItemName AS SubCategoryName,
PM.OilWear AS Quantity, PM.OilPrice AS Price, PM.OilType AS UnitID, '升' AS Unit, PM.OilAmount AS Amount, PM.ApplicationDate, O.OrganizationID, O.OrganizationCode, O.OrganizationName
FROM ProjectQuantity Q
INNER JOIN Project P
ON Q.ProjectID = P.ProjectID
INNER JOIN Organization O
ON P.OrganizationID = O.OrganizationID
INNER JOIN Construction C
ON Q.ConstructionID = C.ConstructionID
INNER JOIN ProjectMachine PM
ON Q.QuantityID = PM.QuantityID
INNER JOIN DictionayItem DM
ON PM.MachineID = DM.ItemID AND DM.DictionayID = 7
INNER JOIN DictionayItem PO
ON PM.OilType = PO.ItemID AND PO.DictionayID = 13
WHERE Q.[State] = 500 AND PM.[State] = 500 AND PM.OilAmount > 0 
UNION
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.CategoryID AS ProjectCategory, P.[State], P.StartDate, P.CompletionDate, P.Budget, P.ApprovedBudget,
Q.Quantity AS ProjectQuantity, C.Unit AS ProjectUnit, Q.Price AS ProjectPrice, Q.Amount AS ProjectAmount, C.ConstructionID, C.ConstructionName,
DataType = 4, DataTypeName = '维修费用', PM.MachineID As Category, DM.ItemName AS CategoryName, PM.MachineID AS SubCategory, '/' AS SubCategoryName,
0 AS Quantity, 0 AS Price, PM.MachineUnitID AS UnitID, '/' AS Unit, PM.MaintenanceCost AS Amount, PM.ApplicationDate, O.OrganizationID, O.OrganizationCode, O.OrganizationName
FROM ProjectQuantity Q
INNER JOIN Project P
ON Q.ProjectID = P.ProjectID
INNER JOIN Organization O
ON P.OrganizationID = O.OrganizationID
INNER JOIN Construction C
ON Q.ConstructionID = C.ConstructionID
INNER JOIN ProjectMachine PM
ON Q.QuantityID = PM.QuantityID
INNER JOIN DictionayItem DM
ON PM.MachineID = DM.ItemID AND DM.DictionayID = 7
WHERE Q.[State] = 500 AND PM.[State] = 500 AND PM.MaintenanceCost > 0 
GO

--创建工程进度表视图
--删除视图
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'ProjectProgress' AND xtype = 'V' )
BEGIN
	DROP VIEW ProjectProgress
END
GO

--创建视图
CREATE VIEW ProjectProgress 
AS
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.[State] AS [State], P.StartDate, P.EndStake, P.CategoryID, P.OrganizationID, 
       C.ConstructionID, C.ConstructionName, C.Unit, Q.PlanQuantity, Q.Quantity
FROM
(
	SELECT ISNULL( PL.ProjectID, PQ.ProjectID ) AS ProjectID, ISNULL( PPI.ConstructionID, PQ.ConstructionID ) AS ConstructionID,
		   ISNULL( PPI.Quantity, 0 ) AS PlanQuantity, ISNULL( PQ.Quantity, 0  ) AS Quantity
	FROM
	(
		SELECT TOP 1 PlanID, ProjectID, Version, RANK() OVER( PARTITION BY ProjectID ORDER BY [Version] DESC ) AS [Rank]
		FROM ProjectPlan
		WHERE [State] = 500
	) PL
	INNER JOIN ProjectPlanItem PPI
	ON PL.PlanID = PPI.PlanID
	FULL OUTER JOIN
	(	
		SELECT ProjectID, ConstructionID, SUM(Quantity) AS Quantity
		FROM ProjectQuantity
		WHERE [State] = 500
		GROUP BY ProjectID, ConstructionID
	) PQ
	ON PL.ProjectID = PQ.ProjectID AND PPI.ConstructionID = PQ.ConstructionID
) AS Q
INNER JOIN Project P
ON Q.ProjectID = P.ProjectID
INNER JOIN Construction C
ON Q.ConstructionID = C.ConstructionID
GO

--创建工程花费表视图
--删除视图
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'ProjectCost' AND xtype = 'V' )
BEGIN
	DROP VIEW ProjectCost
END
GO

--创建工程花费表视图
--删除视图
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'ProjectCost' AND xtype = 'V' )
BEGIN
	DROP VIEW ProjectCost
END
GO

--创建视图
CREATE VIEW ProjectCost
AS  
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.[State] AS [State], P.StartDate, P.CompletionDate,  P.CategoryID, P.OrganizationID,
	   C.ConstructionID, C.ConstructionName, Q.PlanAmount, Q.Amount, Q.PlanTotalAmount, 
	   ISNULL( PQ.TotalAmount, 0  ) AS TotalAmount, PQ.FirstConstructionDate, PQ.LatestConstructionDate
FROM
(
    SELECT ISNULL( PL.ProjectID, PQ.ProjectID ) AS ProjectID, ISNULL( PPI.ConstructionID, PQ.ConstructionID ) AS ConstructionID,
		   ISNULL( PPI.Amount, 0 ) AS PlanAmount, ISNULL( PQ.Amount, 0  ) AS Amount, ISNULL( PL.Amount, 0 ) AS PlanTotalAmount
	FROM
	(
		SELECT TOP 1 PlanID, ProjectID, Amount, [Version], RANK() OVER( PARTITION BY ProjectID ORDER BY [Version] DESC ) AS [Rank]
		FROM ProjectPlan
		WHERE [State] = 500
	) PL
	INNER JOIN ProjectPlanItem PPI
	ON PL.PlanID = PPI.PlanID
	FULL OUTER JOIN
	(	
		SELECT ProjectID, ConstructionID, SUM(Amount) AS Amount
		FROM ProjectQuantity
		WHERE [State] = 500
		GROUP BY ProjectID, ConstructionID
	) PQ
	ON PL.ProjectID = PQ.ProjectID AND PPI.ConstructionID = PQ.ConstructionID
) AS Q
INNER JOIN Project P
ON Q.ProjectID = P.ProjectID
INNER JOIN Construction C
ON Q.ConstructionID = C.ConstructionID
LEFT JOIN
(
	SELECT ProjectID, SUM(Amount) AS TotalAmount, 
		   Min( ApplicationDate ) AS FirstConstructionDate, 
		   MAX( ApplicationDate ) AS LatestConstructionDate
	FROM ProjectQuantity
	WHERE [State] = 500
	GROUP BY ProjectID
) AS PQ
ON Q.ProjectID = PQ.ProjectID
GO

--创建项目价格预警视图
--删除视图
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'ProjectPriceWarning' AND xtype = 'V' )
BEGIN
	DROP VIEW ProjectPriceWarning
END
GO

--创建视图
CREATE VIEW ProjectPriceWarning 
AS
SELECT P.ProjectID, P.ProjectName, P.ProjectCode, P.StartDate, P.EndStake, P.[State], P.CategoryID, P.OrganizationID,
	   Q.Amount, Q.Quantity, Q.ApplicationDate, ISNULL( L.Amount, 0 ) AS LabourAmount, 
	   ISNULL( M.Amount, 0 ) AS MaterialAmount,
	   ISNULL( ME.Amount, 0 ) AS MachineAmount
FROM ProjectQuantity Q
INNER JOIN Project P
ON P.ProjectID = Q.ProjectID
LEFT JOIN
(
	SELECT QuantityID, SUM( Amount ) AS Amount
	FROM ProjectLabour
	WHERE [State] = 500
	GROUP BY QuantityID
) L
ON Q.QuantityID = L.QuantityID
LEFT JOIN
(
	SELECT QuantityID, SUM( Amount ) AS Amount
	FROM ProjectMaterial
	WHERE [State] = 500	
	GROUP BY QuantityID
) AS M
ON M.QuantityID = L.QuantityID
LEFT JOIN
(
	SELECT QuantityID, SUM( Amount ) AS Amount
	FROM ProjectMachine
	WHERE [State] = 500	
	GROUP BY QuantityID
) AS ME
ON ME.QuantityID = L.QuantityID
GO